﻿CREATE VIEW [dbo].[ColumnInfo]
	AS
	
select ISCOL.COLUMN_NAME, 
	case when ISCOL.DATA_TYPE in ('binary', 'char', 'datetime2', 'datetimeoffset', 'nchar', 'nvarchar', 'time', 'varbinary', 'varchar')
		then ISCOL.DATA_TYPE + ' (' + convert(varchar, ISCOL.CHARACTER_MAXIMUM_LENGTH) + ')'
	when ISCOL.DATA_TYPE in ('decimal', 'numeric')
		then ISCOL.DATA_TYPE + ' (' + convert(varchar, ISCOL.NUMERIC_PRECISION) + ', ' + convert(varchar, ISCOL.NUMERIC_SCALE) + ')'
	else
		ISCOL.DATA_TYPE
	end as COLUMN_TYPE,
	SEP.[value] as [Description]
from sys.schemas SCH
inner join sys.tables TBL on TBL.schema_id = SCH.schema_id
inner join sys.columns COL on COL.object_id = TBL.object_id
inner join INFORMATION_SCHEMA.COLUMNS ISCOL
	on	ISCOL.TABLE_SCHEMA = SCH.name
	and	ISCOL.TABLE_NAME = TBL.name
	and ISCOL.COLUMN_NAME = COL.name
left join sys.extended_properties SEP on TBL.object_id = SEP.major_id
                                         and COL.column_id = SEP.minor_id
                                         and SEP.name = 'MS_Description'
where TBL.name = 'DimReseller'
